const fs = require('fs-extra')
const lodash = require('lodash')
const sqlite3 = require('sqlite3').verbose()
const { createPool } = require('generic-pool')
import path from 'path'
import { app, ipcRenderer } from 'electron'

// 创建SQLite数据库连接工厂
const dbFactory = {
  create: function () {
    let dbSlot = 'db/pool.db'
    let dbPath = path.resolve(dbSlot)
    if (!fs.existsSync(dbPath)) {
      if (process.type !== 'renderer') {
        dbPath = path.dirname(app.getAppPath())
      } else {
        dbPath = path.dirname(ipcRenderer.sendSync('getAppPath'))
      }
      dbPath = path.join(dbPath, dbSlot)
    }
    return new sqlite3.Database(dbPath)
  },
  destroy: function (db) {
    db.close()
  },
}

// 创建连接池
const pool = createPool(dbFactory, {
  max: 10, // 最大连接数
  min: 2, // 最小连接数
  acquireTimeoutMillis: 30000, // 获取连接的超时时间（毫秒）
  maxWaitingClients: 20, // 最大等待连接数
})

class Conn {
  async initial() {
    this.db = await pool.acquire()
    return this
  }
  async beginTransaction() {
    let runPromise = new Promise((resolve) => {
      this.db.run('BEGIN TRANSACTION', function (err) {
        resolve(err)
      })
    })
    return await runPromise
  }

  async commit() {
    let runPromise = new Promise((resolve) => {
      this.db.run('COMMIT', function (err) {
        resolve(err)
      })
    })
    return await runPromise
  }

  async rollback() {
    let runPromise = new Promise((resolve) => {
      this.db.run('ROLLBACK', function (err) {
        resolve(err)
      })
    })
    return await runPromise
  }

  bindParams(obj, isUpdated) {
    if (lodash.isObject(obj)) {
      let vals = []
      let keys = Object.keys(obj)
      let keyValues = []
      for (let key of keys) {
        if (isUpdated && key == 'id') {
          continue
        }
        let val = obj[key]
        if (key == 'id' && val == 0) {
          val = null
        }
        if (lodash.isString(val)) {
          vals.push(`'${val}'`)
          keyValues.push(`${key}='${val}'`)
        } else {
          vals.push(`${val}`)
          keyValues.push(`${key}=${val}`)
        }
      }
      if (isUpdated) {
        return keyValues.join(',')
      } else {
        return `(${keys.join(',')}) values (${vals.join(',')})`
      }
    } else {
      return obj
    }
  }

  prepareStatment(statment, params, isUpdated) {
    let stats = []
    if (lodash.isArray(params)) {
      for (let obj of params) {
        stats.push(this.bindParams(obj, isUpdated))
      }
    } else {
      stats.push(this.bindParams(params, isUpdated))
    }
    for (let i = 0; i < stats.length; i++) {
      statment = statment.replace('?', '-------!#!-------')
    }
    let pattern = /set\s+-------!#!-------/i
    for (let stat of stats) {
      if (!isUpdated && pattern.test(statment)) {
        statment = statment.replace(pattern, stat)
      } else {
        statment = statment.replace('-------!#!-------', stat)
      }
    }
    return statment
  }

  async query(statment, params) {
    let lowerStat = statment.toLowerCase()
    if (lowerStat.startsWith('update')) {
      let stat = this.prepareStatment(statment, params, true)
      let runPromise = new Promise((resolve) => {
        this.db.run(stat, function (err) {
          resolve({
            insertId: this.lastID,
            affectedRows: this.changes,
            changedRows: 0,
            err: err,
          })
        })
      })
      return await runPromise
    } else if (
      lowerStat.startsWith('insert') ||
      lowerStat.startsWith('delete')
    ) {
      let stat = this.prepareStatment(statment, params, false)
      let runPromise = new Promise((resolve) => {
        this.db.run(stat, function (err) {
          console.log('insert', stat, err)
          resolve({
            insertId: this.lastID,
            affectedRows: this.changes,
            changedRows: 0,
            err: err,
          })
        })
      })
      return await runPromise
    } else {
      let getAllPromise = new Promise((resolve, reject) => {
        this.db.all(statment, (err, res) => {
          if (!err) {
            resolve(res)
          } else {
            reject([])
          }
        })
      })
      return await getAllPromise
    }
  }
  async release() {
    pool.release(this.db)
  }
}

async function getConn() {
  return await new Conn().initial()
}

export { getConn }
